/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DataAccess;

import java.sql.*;

/**
 *
 * @author vjphl
 */
public class ConnectDatabase {
    protected Connection conn = null;
    
    public CallableStatement castm = null;
    public PreparedStatement pre = null;
    public Statement stat = null;
    public ResultSet rs = null;
    
    protected int mode = NORMAL;

    public static final int PREPARED = 0;
    public static final int CALLABLE = 1;
    public static final int NORMAL = 2;

    protected String classDriver;
    protected String urlConnect = "jdbc:sqlserver://VJPHL-PC:1433;databaseName=PaperPresentation;User=sa;Password=sa";

    public ConnectDatabase() {
        this.classDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    }


    public ConnectDatabase(String Driver) throws Exception {
        this.classDriver = Driver;
    }
      

    public Connection open() throws SQLException, ClassNotFoundException {
        Class.forName(this.classDriver);
        conn = DriverManager.getConnection(urlConnect);
        return conn;
    }

    public void close() throws SQLException {
        this.clear();        
        conn.close();
    }
    
    public void setConnectAutoCommit(boolean chk) throws SQLException {
        this.conn.setAutoCommit(chk);
    }
    
    public void setCommit() throws SQLException {
        this.conn.commit();
    }
    

    public void clear() {
        //
        rs = null;
        pre = null;
        stat = null;
        //
    }
    

    public void setMode(int mode) {
        this.mode = mode;
    }


    public void setStatement(String sql) throws SQLException {
        if (conn != null) {
            if (mode == PREPARED) {
                pre = conn.prepareStatement(sql);
            } else if (mode == CALLABLE) {
                castm = conn.prepareCall(sql);
            } else if (mode == NORMAL) {
                stat = conn.createStatement();
            }
        }
    }

    public ResultSet doSqlQuery(String sql) throws SQLException {
        rs = null;
        //
        if (mode == NORMAL) {
            stat = conn.createStatement();
            rs = stat.executeQuery(sql);
        }
        //
        return rs;
    }

    public ResultSet doQuery() throws SQLException {
 
        rs = null;

        if (pre != null && mode == PREPARED) {
            rs = pre.executeQuery();
            pre.clearParameters();
        } else if (castm != null && mode == CALLABLE) {
            rs = castm.executeQuery(); 
            castm.clearParameters();
        } 

        return rs;
    }

    public int doUpdate(String sql) throws SQLException {
        if (mode == NORMAL) {
            stat = conn.createStatement();
            return stat.executeUpdate(sql);
        } else {
            return 0;
        }
    }

    public int doUpdate() throws SQLException {
        //
        int rows = 0;
        //
        if (mode == PREPARED) {
            rows = pre.executeUpdate();
        }
        //
        if (mode == CALLABLE) {
            rows = castm.executeUpdate();
        }        
        //
        return rows;
    }

    public void setDouble(int idx, double value) throws SQLException {
        pre.setDouble(idx, value);
    }

    public void setInt(int idx, int value) throws SQLException {
        pre.setInt(idx, value);
    }

    public void setString(int idx, String value) throws SQLException {
        pre.setString(idx, value);
    }
}
